package com.MonkWorks.timedormitoryDB;
/**
 * This is used to persist and retrieve all values from the local database
 */
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.MonkWorks.clockDTO.Job;
import com.MonkWorks.clockDTO.User;

public class TimeDB extends SQLiteOpenHelper implements ItimeProvider {

	private static final String DATABASE_NAME = "monkworksDB";
	private static final int DATABASE_VERSION = 1;
	private static final String ENTRY_ID = "_id";
	private static final String USER_ID = "userID";
	private static final String JOB_ID = "jobID";
	private static final String LATITUDE = "latitude";
	private static final String LONGITUDE = "longitude";
	// Clock action key: 1-clock in; 2- clock out
	private static final String CLOCK_ACTION = "clockAction";
	private static final String TIME = "time";
	private static final String TIMEACTIVITY = "TIMEACTIVITY";
	private static final String PASSWORD = "password";
	private static final String USERTYPE = "userType";
	private static final String USERS = "USERDB";
	private static final String FIRST_NAME = "firstName";
	private static final String LAST_NAME = "lastName";
	private static final String EMAIL = "email";
	private static final String PHONE = "phoneNum";
	private static final String JOBS = "JOBDB";
	private static final String JOB_NAME = "jobName";
	private static final String JOB_DESC = "jobDescription";
	private static final String ADDRESS = "address";
	private static final String CREATED_BY = "createdBy";
	private final String CLASS_NAME = getClass().getName();

	public TimeDB(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);

	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// Create a database table
		String createMonkworksDB = "CREATE TABLE " + TIMEACTIVITY + " ("
				+ ENTRY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, "
				+ USER_ID + " TEXT NOT NULL, " + JOB_ID + " TEXT NOT NULL, "
				+ LATITUDE + " INTEGER DEFAULT 0, " + LONGITUDE
				+ " INTEGER DEFAULT 0, " + CLOCK_ACTION + " TEXT NOT NULL, "
				+ TIME + " TEXT NOT NULL, " + ADDRESS + " TEXT NOT NULL);";
		String createUserAccountDB = "CREATE TABLE " + USERS + "(" + ENTRY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " + USER_ID
				+ " TEXT NOT NULL, " + PASSWORD + " TEXT NOT NULL, " + USERTYPE
				+ " TEXT, " + FIRST_NAME + " TEXT NOT NULL, " + LAST_NAME
				+ " TEXT NOT NULL, " + EMAIL + " TEXT, " + PHONE + " TEXT);";
		String createJobDB = "CREATE TABLE " + JOBS + "(" + ENTRY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, " + JOB_NAME
				+ " TEXT NOT NULL, " + JOB_DESC + " TEXT, " + CREATED_BY
				+ " TEXT NOT NULL, " + ADDRESS + " TEXT NOT NULL, " + LATITUDE
				+ " INTEGER DEFAULT 0, " + LONGITUDE + " INTEGER DEFAULT 0);";
		// log it
		Log.d(CLASS_NAME, "Database creation");

		// execute statement
		db.execSQL(createMonkworksDB);
		db.execSQL(createUserAccountDB);
		db.execSQL(createJobDB);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

	public void clockIn(User user, String time, Job job, double Latitude,
			double Longitude) throws Exception {
		// Save clock in data to content values. Hard code clock action to 1 to
		// define clocking in action
		ContentValues clockin = new ContentValues();
		// hard code this for now. Need to set up object but not right now. Just
		// testing SQLight
		clockin.put(USER_ID, user.getUsername());
		clockin.put(JOB_ID, job.getName());
		clockin.put(LATITUDE, Latitude);
		clockin.put(LONGITUDE, Longitude);
		clockin.put(CLOCK_ACTION, "Clocked In");
		clockin.put(TIME, time);
		clockin.put(ADDRESS, job.getAddresss());

		// store in db
		long entryID = getWritableDatabase().insert(TIMEACTIVITY, LATITUDE,
				clockin);

		// update user with id
		// TODO: haven't set up objects yet

		// log clockin
		Log.d(CLASS_NAME, "ClockIn");
	}

	public void clockOut(User user, String time, Job job, double Latitude,
			double Longitude) throws Exception {
		// Save clock out data to content values. Hard code clock action to 2 to
		// define clocking out action
		ContentValues clockout = new ContentValues();
		// hard code this for now. Need to set up object but not right now. Just
		// testing SQLight
		clockout.put(USER_ID, user.getUsername());
		clockout.put(JOB_ID, job.getName());
		clockout.put(LATITUDE, Latitude);
		clockout.put(LONGITUDE, Longitude);
		clockout.put(CLOCK_ACTION, "Clocked Out");
		clockout.put(TIME, time);
		clockout.put(ADDRESS, job.getAddresss());

		// store in db
		long entryID = getWritableDatabase().insert(TIMEACTIVITY, TIME,
				clockout);

		// log clockin
		Log.d(CLASS_NAME, "ClockOut");
	}

	public List<String> fetchJobByUser(String username) throws Exception {

		// create return value
		ArrayList<String> getJobs = new ArrayList<String>();

		// query to retrieve jobs created by user
		String findJobSQL = "SELECT * FROM " + JOBS + " WHERE " + CREATED_BY
				+ " ='" + username + "'";

		Log.d(CLASS_NAME, "findJobSQL");

		// bring in the cursor
		Cursor cursor = getReadableDatabase().rawQuery(findJobSQL, null);

		// iterate
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results");

			// start the cursor at first spot
			cursor.moveToFirst();
			// iterate over results
			while (!cursor.isAfterLast()) {

				// populate job objects

				getJobs.add(cursor.getString(1));

				// move the cursor to the next value
				cursor.moveToNext();

			}
			// close the cursor
			cursor.close();

		}
		// return the result
		return getJobs;
	}

	@Override
	public boolean Logon(String Username, String Password) throws Exception {
		// attempt to locate user in database
		String logonSQL = "SELECT " + USER_ID + ", " + PASSWORD + " FROM "
				+ USERS + " WHERE " + USER_ID + "='" + Username + "' AND "
				+ PASSWORD + "='" + Password + "'";

		Log.d("Logging On", "LogonSQL");

		// bring in the cursor to read results if any
		Cursor cursor = getReadableDatabase().rawQuery(logonSQL, null);

		// check to see if any results were returned
		if (cursor.getCount() > 0) {
			// close the cursor
			cursor.close();
			// if we get a result, log the user in
			return true;

		} else {
			// if no results, return false
			return false;
		}

	}

	@Override
	public User fetchUserByUsername(String username) throws Exception {
		// search the User table for match on username
		String fetchUserSQL = "SELECT * FROM " + USERS + " WHERE " + USER_ID
				+ "='" + username + "'";
		Log.i(CLASS_NAME, "finding username");

		// bring in the cursor to read results if any
		Cursor cursor = getReadableDatabase().rawQuery(fetchUserSQL, null);

		// check to see if any results were returned
		if (cursor.getCount() > 0) {
			// move the cursor to the result which should only be 1
			cursor.moveToFirst();

			// create the user object and return it
			User foundUser = new User();
			foundUser.setUsername(cursor.getString(1));
			foundUser.setPassword(cursor.getString(2));
			foundUser.setFirstName(cursor.getString(4));
			foundUser.setLastName(cursor.getString(5));
			foundUser.setEmail(cursor.getString(6));

			return foundUser;
		} else {
			Log.e(CLASS_NAME, "find username");
			throw new IOException("Cannot find user");
		}
	}

	@Override
	public void saveUser(User user) throws Exception {
		// first check to see if user already exists
		String userCheckSQL = "SELECT " + USER_ID + " FROM " + USERS
				+ " WHERE " + USER_ID + "='" + user.getUsername() + "'";

		// bring in the cursor to read results if any
		Cursor cursor = getReadableDatabase().rawQuery(userCheckSQL, null);

		// check to see if a value is returned. If not persist
		if (cursor.getCount() > 0) {
			Log.e(DATABASE_NAME, "User already exsist");
			throw new IOException("User already exsist");
		} else {
			// save a new user to the database
			try {
				ContentValues newUser = new ContentValues();
				newUser.put(USER_ID, user.getUsername());
				newUser.put(FIRST_NAME, user.getFirstName());
				newUser.put(LAST_NAME, user.getLastName());
				newUser.put(PASSWORD, user.getPassword());
				newUser.put(EMAIL, user.getemail());

				// store it in the DB
				long entryID = getWritableDatabase().insert(USERS, FIRST_NAME,
						newUser);
			} catch (Exception e) {
				Log.e(DATABASE_NAME, "Create User");
				throw new IOException("Database Issue");
			}
		}
	}

	// this checks to see if an admin account exsist everytime the program
	// starts. If it doesn't, this will automatically generate the admin account
	public void generateAdmin() throws IOException {
		// query to find admin account
		String adminCheck = "SELECT " + USER_ID + " FROM " + USERS + " WHERE "
				+ USER_ID + "='Admin'";

		// get the cursor
		Cursor cursor = getReadableDatabase().rawQuery(adminCheck, null);

		// check to see if any results were returned
		if (cursor.getCount() > 0) {
			// close the cursor if admin is found
			cursor.close();
		} else {
			// create admin account if no admin account is found
			try {
				ContentValues newUser = new ContentValues();
				newUser.put(USER_ID, "Admin");
				newUser.put(FIRST_NAME, "Administrator");
				newUser.put(LAST_NAME, "No Value");
				newUser.put(PASSWORD, "1234");
				newUser.put(EMAIL, "No Value");
				Log.d(CLASS_NAME, "Create Admin");
				// store it in the DB
				long entryID = getWritableDatabase().insert(USERS, FIRST_NAME,
						newUser);
			} catch (Exception e) {
				Log.e(DATABASE_NAME, "Create Admin");
				throw new IOException("Database Issue");
			}
		}
	}

	@Override
	public void saveJob(Job job, User user) throws Exception {
		// check to see if job with same name already exist
		String jobCheckSQL = "SELECT * FROM " + JOBS + " WHERE " + JOB_NAME
				+ " ='" + job.getName() + "'";

		// read the query with a cursor
		Cursor cursor = getReadableDatabase().rawQuery(jobCheckSQL, null);

		// check to see if results were returned
		if (cursor.getCount() > 0) {
			// throw exception if job already exist
			Log.e(CLASS_NAME, "job already exist");
			throw new Exception("Job already exist");
		}
		// if no job exist of that name, persist job
		else {
			// save a job to the database
			try {
				ContentValues newJob = new ContentValues();
				newJob.put(JOB_NAME, job.getName());
				newJob.put(JOB_DESC, job.getDescription());
				newJob.put(ADDRESS, job.getAddresss());
				newJob.put(CREATED_BY, user.getUsername());
				newJob.put(LONGITUDE, job.getLatitude());
				newJob.put(LATITUDE, job.getLatitude());

				// store it in the JOBDB
				long entryID = getWritableDatabase().insert(JOBS, JOB_NAME,
						newJob);
				
				
			} catch (Exception e) {
				throw new IOException("unable to save job");
			}
		}
	}

	@Override
	public List<Job> findJobByName(String name) {
		// create a return value.
		List<Job> allJobs = new ArrayList<Job>();

		// query to find job by name
		String fetchJobSQL = "SELECT * FROM " + JOBS + " WHERE " + JOB_NAME
				+ "='" + name + "'";
		Log.i(CLASS_NAME, "finding username");

		// bring in the cursor to read results if any
		Cursor cursor = getReadableDatabase().rawQuery(fetchJobSQL, null);

		// move the cursor to the result which should only be 1
		cursor.moveToFirst();
		if (cursor.getCount() > 0) {

			// check to see if any results were returned
			while (!cursor.isAfterLast()) {
				// create the job object and return it
				Job foundJob = new Job();

				foundJob.setJobID(cursor.getInt(0));
				foundJob.setName(cursor.getString(1));
				foundJob.setDescription(cursor.getString(2));
				foundJob.setCreatedby(cursor.getString(3));
				foundJob.setAddress(cursor.getString(4));
				foundJob.setLatitude(cursor.getInt(5));
				foundJob.setLongitude(cursor.getInt(6));

				// add it to the return list
				allJobs.add(foundJob);

				// move the cursor to the next value
				cursor.moveToNext();
			}
			// close the cursor
			cursor.close();
		} else {
			Log.e(CLASS_NAME, "Unable to find job by name");
		}
		return allJobs;
	}

	@Override
	public List<String> findActivityByJob(String name) throws IOException {
		// create a return value
		List<String> allActivities = new ArrayList<String>();

		// query to find all activities based on Job name
		String findActSQL = "SELECT * FROM " + TIMEACTIVITY + " WHERE "
				+ JOB_ID + " ='" + name + "' ORDER BY " + ENTRY_ID + " DESC";
		Log.i(CLASS_NAME, "finding activity");

		// bring in the cursor to read results
		Cursor cursor = getReadableDatabase().rawQuery(findActSQL, null);

		// move the cursor to the first result
		cursor.moveToFirst();

		// check to see if there are results, if so populate a list
		if (cursor.getCount() > 0) {
			// begin to move the cursor to populate results
			while (!cursor.isAfterLast()) {
				// create a clockActivity object
				// ClockActivity act = new ClockActivity();
				/*
				 * act.setActivityID(cursor.getInt(0));
				 * act.setUsername(cursor.getString(1));
				 * act.setJobName(cursor.getString(2));
				 * act.setLatitude(cursor.getDouble(3));
				 * act.setLongititude(cursor.getDouble(4));
				 * act.setClockAction(cursor.getInt(5));
				 * act.setTime(cursor.getString(6));
				 */
				allActivities.add(cursor.getString(5) + " @ "
						+ cursor.getString(6));

				// add it to the return list
				// allActivities.add(act);

				// move cursor to next result
				cursor.moveToNext();

			}
			// close the cursor when done iterating
			cursor.close();
		} else {
			Log.e(CLASS_NAME, "Cannot retrieve Activities");
			throw new IOException(
					"Cannot retrieve activities, DB inconsistency error");
		}
		// return list
		return allActivities;
	}

	@Override
	public List<String> fetchAllJobs() {
		// create return value
		ArrayList<String> getJobs = new ArrayList<String>();

		// query to retrieve jobs created by user
		String findallJobSQL = "SELECT * FROM " + JOBS + "";

		Log.d(CLASS_NAME, "findJobSQL");

		// bring in the cursor
		Cursor cursor = getReadableDatabase().rawQuery(findallJobSQL, null);

		// iterate
		if (cursor.getCount() > 0) {
			Log.i(CLASS_NAME, cursor.getCount() + " results");

			// start the cursor at first spot
			cursor.moveToFirst();
			// iterate over results
			while (!cursor.isAfterLast()) {

				// populate job objects

				getJobs.add(cursor.getString(1));

				// move the cursor to the next value
				cursor.moveToNext();

			}
			// close the cursor
			cursor.close();

		}
		// return the result
		return getJobs;
	}

	@Override
	public List<String> fetchAllUsers() {
		// create return value
				ArrayList<String> getUsers = new ArrayList<String>();

				// query to retrieve users
				String findallUsersSQL = "SELECT * FROM " + USERS + "";

				Log.d(CLASS_NAME, "findJobSQL");

				// bring in the cursor
				Cursor cursor = getReadableDatabase().rawQuery(findallUsersSQL, null);

				// iterate
				if (cursor.getCount() > 0) {
					Log.i(CLASS_NAME, cursor.getCount() + " results");

					// start the cursor at first spot
					cursor.moveToFirst();
					// iterate over results
					while (!cursor.isAfterLast()) {

						
						//populate list
						getUsers.add(cursor.getString(1));

						// move the cursor to the next value
						cursor.moveToNext();

					}
					// close the cursor
					cursor.close();

				}
				// return the result
				return getUsers;
	}

	@Override
	public void updateUser(String username, String password, String firstName, String lastName, String email) throws Exception {
		try{
		//create content values
		ContentValues values = new ContentValues();
		values.put(USER_ID, username);
		values.put(PASSWORD, password);
		values.put(FIRST_NAME, firstName);
		values.put(LAST_NAME, lastName);
		values.put(EMAIL, email);
		
		//update the database
		long sqlId = getWritableDatabase().update(USERS, values, USER_ID + "='" + username + "'", null);
		}
		catch(Exception e)
		{
			Log.d(CLASS_NAME, "Cannot Update User");
			throw new Exception("Cannot Update User");
		}
	}

	@Override
	public boolean clockCheck(Job job) {
		//query to search the database for the last job activity based on job name and Table id
		String findLastActivitySQL = "SELECT * FROM " + TIMEACTIVITY + " WHERE jobID ='" + job.getName() + "' AND _id = (SELECT MAX(_id) FROM TIMEACTIVITY);";
		
		// bring in the cursor
		Cursor cursor = getReadableDatabase().rawQuery(findLastActivitySQL, null);
		
		// start the cursor at first spot
		cursor.moveToFirst();
		
		//initialize a random string variable
		String clockOnchk;
		try
		{
		//Retrieve the clock action value
		clockOnchk = cursor.getString(5);
		}
		catch (Exception e)
		{
			return true;
		}
		
		//check to see if job is already clocked on 
		if(clockOnchk.equals("Clocked In"))
		{
			return false;
		}
		else
			{
			return true;
			}
	}

	@Override
	public boolean clockOutCheck(Job job) {
		//query to search the database for the last job activity based on job name and Table id
		String findLastActivitySQL = "SELECT * FROM " + TIMEACTIVITY + " WHERE jobID ='" + job.getName() + "' AND _id = (SELECT MAX(_id) FROM TIMEACTIVITY);";
		
		// bring in the cursor
		Cursor cursor = getReadableDatabase().rawQuery(findLastActivitySQL, null);
		
		// start the cursor at first spot
		cursor.moveToFirst();
		
		//initialize a random string variable
		String clockOutchk;
		
		//Retrieve the clock action value
		try
		{
		clockOutchk = cursor.getString(5);
		}
		catch(Exception e)
		{
			return false;
		}
		//check to see if job is already clocked out 
		if(clockOutchk.equals("Clocked Out"))
		{
			return false;
		}
		else
		{
			return true;
		}
	}

}
